DROP TABLE IF EXISTS jms_ods.yl_tmsnew_tms_out_warehouse_sh;
CREATE EXTERNAL TABLE jms_ods.yl_tmsnew_tms_out_warehouse_sh(
 ID bigint comment "主键自增ID",  --NUMBER(11)
 SHIFT_CODE string comment "班次编码",  --NVARCHAR2(30)
 NETWORK_CODE string comment "网点编码",  --NVARCHAR2(30)
 NETWORK_ID bigint comment "网点ID",  --NUMBER(11)
 NETWORK_NAME string comment "网点名称",  --NVARCHAR2(30)
 UP_NETWORK_CODE string comment "当前网点的上级网点",  --NVARCHAR2(30)
 UP_DISTRIBUTION_CODE string comment "当前网点的上级分拨",  --NVARCHAR2(30)
 NETWORK_TYPE string comment "网点类型编码",  --NVARCHAR2(30)
 NETWORK_TYPE_NAME string comment "网点类型名称",  --NVARCHAR2(30)
 WAREHOUSE_START_TIME string comment "出仓班次开始时间",  --NVARCHAR2(10)
 WAREHOUSE_END_TIME string comment "出仓班次结束时间",  --NVARCHAR2(10)
 APPLY_DAY string comment "适用工作日,以逗号隔开(1,2,3)",  --NVARCHAR2(20)
 START_TIME timestamp comment "生效日期",  --DATE
 END_TIME timestamp comment "失效日期",  --DATE
 STATUS smallint comment "状态 0：待生效  1：生效 2：失效",  --NUMBER(4)
 CREATE_BY_NAME string comment "创建人姓名",  --NVARCHAR2(20)
 CREATE_TIME timestamp comment "创建时间",  --DATE
 LATEST_ARRIVAL_TIME string comment "最晚到车时间",  --NVARCHAR2(10)
 DISPATCH_TIME bigint comment "派件时长(分钟)",  --NUMBER(11)
 DISPATCH_END_TIME string comment "派件结束时间",  --NVARCHAR2(10)
 UPDATE_BY_NAME string comment "更新人姓名",  --NVARCHAR2(20)
 UPDATE_TIME timestamp comment "更新时间",  --DATE
 IS_DELETE smallint comment "是否已删除 1：未删除 2已删除",  --NUMBER(4)
 FRANCHISEE_CODE string comment "加盟商网点编码",  --NVARCHAR2(30)
 FRANCHISEE_NAME string comment "加盟商网点名称",  --NVARCHAR2(60)
 SPAN_DAYS smallint comment "跨越天数",  --NUMBER(3)
 MASTER_SHIFT_ID bigint comment "主班次编码",  --NUMBER(11)
 REMARK string comment "修改原因和备注",  --NVARCHAR2(500)
 CREATE_BY bigint comment "创建人",  --NUMBER(30)
 UPDATE_BY bigint comment "更新人",  --NUMBER(30)
 UPDATE_BY_CODE string comment "更新人",  --NVARCHAR2(30)
 CREATE_BY_CODE string comment "创建人",  --NVARCHAR2(30)
 SHIFTS bigint comment "班次",  --NUMBER(11)
 SHIFT_NAME string comment "班次名称"  --NVARCHAR2(30)
 )
 COMMENT '派仓派件班次'
 PARTITIONED BY (dt STRING COMMENT '更新/创建日期 (yyyy-MM-dd)')
 STORED AS PARQUET
 LOCATION '/dw/hive/jms_ods.db/external/yl_tmsnew_tms_out_warehouse_sh'
 TBLPROPERTIES (
 'discover.partitions'='false',
 'parquet.column.index.access'='true'
 );

MSCK REPAIR TABLE jms_ods.yl_tmsnew_tms_out_warehouse_sh;
alter table jms_ods.yl_tmsnew_tms_out_warehouse_sh add columns (IS_ENABLE bigint comment'启用状态：3 待启用；1 启用；2 禁用',EXCEPTION_DATA bigint comment'0 正常，1、收件结束时间维护异常 2、派件班次未衔接散货支线') cascade;
